package org.vectrics.user;

import java.util.Collection;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.vectrics.DataException;
import org.vectrics.SystemException;
import org.vectrics.common.db.Criteria;
import org.vectrics.common.util.DateSync;
import org.vectrics.common.util.StringUtils;
import org.vectrics.company.Company;
import org.vectrics.company.CompanyPersonGroupType;
import org.vectrics.domain.Domain;
import org.vectrics.employee.Employee;
import org.vectrics.hibernate.Filter;
import org.vectrics.hibernate.Selector;
import org.vectrics.hibernate.ThreadProperties;
import org.vectrics.location.Location;
import org.vectrics.provider.CoreServiceLocator;
import org.vectrics.recruiter.RecruitServiceLocator;
import org.vectrics.recruiter.job.JobFunction;

public class PersonSearcher {
	private transient Logger log = Logger.getLogger(this.getClass());

	public List<Person> findPersons(PersonSearchCriteria criteria, int startIndex, int endIndex) {
		log.debug("findPersons(criteria, startIndex, endIndex)");
		Session session = null;
		List personList = null;
		try {
			session = ThreadProperties.getSession();

			Filter filter = getFilterForCriteria(criteria, true);
			filter.addSelectField("distinct(person)");
			filter.addOrderBy("person.lastName");

			Query query = null;
			query = filter.createQuery(session);
			if (log.isDebugEnabled())
				log.debug("Statment = " + filter.getStatement());


			if ((endIndex >= 0) && (startIndex >= 0)) {
				query.setMaxResults(1 + endIndex - startIndex);
				query.setFirstResult(startIndex);
			}
			personList = query.list();
		}
		catch (Exception ex) {
			log.error("createCompany() - exception", ex);
			throw new DataException(ex);
		}
		finally {
		}
		log.debug("done with findPersons(criteria, startIndex, endIndex)");
		return(personList);
	}

	
	public int findPersonCount(PersonSearchCriteria criteria) {
		log.debug("findPersonCount(criteria)");
		int count = 0;
		Session session = null;
		session = ThreadProperties.getSession();

		Filter filter = getFilterForCriteria(criteria, false);
		filter.addSelectField("count(distinct person.id)");

		
		
		Query query = null;
		query = filter.createQuery(session);

		if (log.isDebugEnabled()) {
			log.debug("Statement = " + filter.getStatement());
		}
		count = 0;

		Object obj = query.iterate().next();
		Long countInt = (Long)obj;
		count = countInt.intValue();
		if (log.isDebugEnabled())
		    log.debug("done with findPersonCount(criteria, startIndex, endIndex), count = " + count);

		return(count);
	}


	/**
	 * @return
	 */
	public Filter getFilterForCriteria(PersonSearchCriteria criteria, boolean ordered) {
		if (log.isDebugEnabled())
			log.debug("getFilterForCriteria() - criteria = " + criteria);
		
		Filter filter = new Filter();
		filter.setConjunction(Selector.CONJUNTION_AND);
		filter.addFrom("Person", "person");
		if (ordered)
		    filter.addOrderBy("person.lastName, person.firstName");
		
		
		if ((criteria.getLocations() != null) && (criteria.getLocations().size() > 0)) {
			log.debug("Adding locations to query");
			Iterator iter = criteria.getLocations().iterator();
			if (iter.hasNext()) {
				Selector locationSelector = filter.getSelector()
					.addChildSelector("Location Selector", Selector.CONJUNTION_OR);
				while (iter.hasNext()) {
					Location location = (Location)iter.next();
					this.addPersonLocationAndChildrenToSelector(location, locationSelector);
				}
			}
		}

		if (criteria.isNotEmployees()){
			List<Employee> employees = RecruitServiceLocator.getCompanyService().findAllEmployees();
			
			if (!employees.isEmpty()) {
				String hql ="person.id not in ( "; 	
				Iterator<Employee> it = employees.iterator();
				while( it.hasNext() ) {
					Employee employee  = it.next();
					hql += employee.getPerson().getId().toString();
					if (it.hasNext()) {
						hql += " , ";
					}
				}
				filter.addCondition(hql+" )");
			}
		}

		if (criteria.getShowSystemUsers() == false) {
			filter.addCondition("person.systemUser='F'");
		}
		
		if (criteria.getEmployeesOnly()) {
			filter.addFrom("CompanyPersonRole", "companyPersonRole");
			if (criteria.getCompanyList().size() > 0) {
				Selector empCoSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
				Iterator iter = criteria.getCompanyList().iterator();
				while (iter.hasNext()) {
					Company company = (Company)iter.next();
					Selector subSelector = empCoSelector.addChildSelector(Selector.CONJUNTION_AND);
					subSelector.addCondition("person.id=companyPersonRole.companyPerson.person.id");
					subSelector.addParameterCondition("companyPersonRole.companyPerson.company.id",
							"=", company.getId());
					subSelector.addCondition("companyPersonRole.voided='F'");
					subSelector.addCondition("companyPersonRole.companyPerson.voided='F'");
				}
			} else {
				log.debug("Find all employees for companies belonging to the domains");
				if (criteria.getDomains().size() == 0)
					throw new SystemException("No companies specified and now data owners" +
							" specified for criteria that find employees");
				Selector empDoSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
				Iterator iter = criteria.getDomains().iterator();
				while (iter.hasNext()) {
					Domain domain = (Domain)iter.next();
					Selector subSelector = empDoSelector.addChildSelector(Selector.CONJUNTION_AND);
					subSelector.addCondition("person.id=companyPersonRole.companyPerson.person.id");
					subSelector.addParameterCondition("companyPersonRole.companyPerson.company.domain.id",
							"=", domain.getId());
					subSelector.addCondition("companyPersonRole.voided='F'");
					subSelector.addCondition("companyPersonRole.companyPerson.voided='F'");
				}
			}
		} 
		
		
		if (StringUtils.isNullOrBlank(criteria.getEmail()) == false) {
			Selector emailSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
			emailSelector.addParameterCondition("person.contactInfo.email",
					"like", criteria.getEmail());
			emailSelector.addParameterCondition("person.contactInfo.email2",
					"like", criteria.getEmail());
			emailSelector.addParameterCondition("person.contactInfo.email",
					"like", criteria.getEmail() + "%");
			emailSelector.addParameterCondition("person.contactInfo.email2",
					"like", criteria.getEmail() + "%");
		}

		if (StringUtils.isNullOrBlank(criteria.getAnyPhone()) == false) {
			Selector phoneSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
			phoneSelector.addParameterCondition("person.contactInfo.homePhone",
					"=", criteria.getAnyPhone());
			phoneSelector.addParameterCondition("person.contactInfo.workPhone",
					"=", criteria.getAnyPhone());
			phoneSelector.addParameterCondition("person.contactInfo.alternatePhone",
					"=", criteria.getAnyPhone());
			phoneSelector.addParameterCondition("person.contactInfo.cellPhone",
					"=", criteria.getAnyPhone());
			phoneSelector.addParameterCondition("person.contactInfo.fax",
					"=", criteria.getAnyPhone());
			phoneSelector.addParameterCondition("person.contactInfo.fax",
					"like", criteria.getAnyPhone() + "%");
		}

		if (StringUtils.isNullOrBlank(criteria.getFirstName()) == false) {
			filter.getSelector().addParameterCondition("person.firstName",
					"like", criteria.getFirstName() + "%");
		}

		if (StringUtils.isNullOrBlank(criteria.getLastName()) == false) {
			log.debug("Looking for lastName = " + criteria.getLastName());
			filter.addParameterCondition("person.lastName",
					"like", criteria.getLastName() + "%");
		}

		if ((criteria.getPrivileges() != null) && (criteria.getPrivileges().size() > 0)) {
			filter.addFrom("UserPrivilege", "userPrivilege");
			filter.addColumnToColumnComparison("userPrivilege.person.id", "=", "person.id");

		    //String roleList = "";
		    //  ( (priv.role1 and priv.do1) or (priv.role1 and priv.do1.parent) or (priv.role1 and priv.do2)
		    
	        Selector roleSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
	        Iterator domainIter = criteria.getDomains().iterator();
	        while (domainIter.hasNext()) {
	        	Domain domain = (Domain)domainIter.next();
			    Iterator iter = criteria.getPrivileges().iterator();
			    while (iter.hasNext()) {
			        String roleName = (String)iter.next();
			        Selector subRoleSelector = roleSelector.addChildSelector(Selector.CONJUNTION_AND);
			        subRoleSelector.addParameterCondition("userPrivilege.privilegeType", "=", roleName);
			        subRoleSelector.addParameterCondition("userPrivilege.domain.id", "=", domain.getId());
			        
			        log.debug("Check to see if any parent data owners have the privilege as global");
			        Domain parentDomain = domain.getParent();
			        while (parentDomain != null) {
				        Selector parentRoleSelector = roleSelector.addChildSelector(Selector.CONJUNTION_AND);
				        parentRoleSelector.addParameterCondition("userPrivilege.privilegeType", "=", roleName);
				        parentRoleSelector.addParameterCondition("userPrivilege.domain.id", "=", parentDomain.getId());
				        parentRoleSelector.addCondition("userPrivilege.global='T'");
			        	
			        	parentDomain = parentDomain.getParent();
			        }
			        
			    }
	        }
		}

		if ((criteria.getEmployeesOnly() == false) && (criteria.getDomains() != null) 
				&& (criteria.getDomains().size() > 0)) {
			Selector domainSelector = null;
			domainSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);

			Set domainsAndParents = new HashSet();
			
			Iterator iter = criteria.getDomains().iterator();
			while (iter.hasNext()) {
				Domain domain = (Domain)iter.next();
				domainsAndParents.add(domain);
				if (criteria.getShowParentDomainPeople()) {
					Domain parent = domain.getParent();
					while (parent != null) {
						domainsAndParents.add(parent);
						parent = parent.getParent();
					}
				}
			}
			
			iter = domainsAndParents.iterator();
			while (iter.hasNext()) {
				Domain domain = (Domain)iter.next();
				domainSelector.addParameterCondition("person.domain.id", "=", domain.getId());
			}
			
		} else {
			log.debug("No data owners specified");
			//throw new SystemException("No domains specified in PersonSelectCriteria");
		}
		
		if (criteria.getGroupTypeList().size() > 0) {
			filter.addFrom("CompanyPersonGroup", "companyPersonGroup");
			Selector groupSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
			Iterator iter = criteria.getGroupTypeList().iterator();
			while (iter.hasNext()) {
				CompanyPersonGroupType groupType = (CompanyPersonGroupType)iter.next();
				if (criteria.getCompanyList().size() > 0) {
					Selector companyGroupSelector = groupSelector.addChildSelector(Selector.CONJUNTION_OR);
					Iterator companyIter = criteria.getCompanyList().iterator();
					while (companyIter.hasNext()) {
						Selector companyGroupSubSelector = companyGroupSelector.addChildSelector(Selector.CONJUNTION_AND);
						Company company = (Company)companyIter.next();
						companyGroupSubSelector.addParameterCondition("companyPersonGroup.companyPersonGroupType.code", "=", groupType.getCode());
						companyGroupSubSelector.addParameterCondition("companyPersonGroup.companyPersonRole.companyPerson.company.id", "=", company.getId());
						companyGroupSubSelector.addCondition("companyPersonGroup.companyPersonRole.companyPerson.person.id=person.id");
						companyGroupSubSelector.addCondition("companyPersonGroup.companyPersonRole.dateEnd is null");
					}
				} else {
					Selector companyGroupSelector = groupSelector.addChildSelector(Selector.CONJUNTION_AND);
					companyGroupSelector.addParameterCondition("companyPersonGroup.companyPersonGroupType.code", "=", groupType.getCode());
					companyGroupSelector.addCondition("companyPersonGroup.companyPersonRole.companyPerson.person.id=person.id");
					companyGroupSelector.addCondition("companyPersonGroup.companyPersonRole.dateEnd is null");
				}
			}
			
		}
		
		if ((criteria.getCompanyList().size() > 0) 
			&& (criteria.getGroupTypeList().size() == 0)) {
			Iterator iter = criteria.getCompanyList().iterator();
			while (iter.hasNext()) {
				Company company = (Company)iter.next();	
				filter.addFrom("CompanyPerson", "companyPerson");
				filter.addCondition("companyPerson.person.id=person.id");
				filter.addParameterCondition("companyPerson.company.id", "=", company.getId());
				filter.addParameterCondition("companyPerson.dateStart", "<", DateSync.getCurrentTime());
				
				Selector dateEndSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
				dateEndSelector.addParameterCondition("companyPerson.dateEnd", ">", DateSync.getCurrentTime());
				dateEndSelector.addCondition("companyPerson.dateEnd is null");
	
				if (criteria.getPersonPrivilegeGroupTypes() == null) {
				} else if (criteria.getPersonPrivilegeGroupTypes().size() > 0) {
					filter.addFrom("PersonPrivilegeGroup", "personPrivilegeGroup");
					filter.addCondition("person.id=personPrivilegeGroup.person.id");
					Iterator grpIter = criteria.getPersonPrivilegeGroupTypes().iterator();
					while (grpIter.hasNext()) {
						Selector grpSelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
						PersonPrivilegeGroupType groupType = (PersonPrivilegeGroupType)grpIter.next();
						grpSelector.addParameterCondition("personPrivilegeGroup.personPrivilegeGroupType.code", 
									"=", groupType.getCode());
						Iterator doIter = criteria.getDomains().iterator();
						while (doIter.hasNext()) {
							Domain domain = (Domain)doIter.next();
							grpSelector.addParameterCondition("personPrivilegeGroup.domain.id", "=", domain.getId());
							// Need to also add priv group for parent data owner if it is null
							addParentDomainPrivGroup(grpSelector, domain, groupType);
						}
					}
				}
			}
		}
		
		// Filtro de genero
		if ((criteria.getGender()!=null)&&(!criteria.getGender().equals(""))){
			filter.addParameterCondition("person.gender","=",criteria.getGender());
		}
		
		// Si compania o post no es nulo tengo que agregar un inner join 
		if (((criteria.getCompany()!=null)&&(!criteria.getCompany().equals("")))||
				((criteria.getPost()!=null)&&(!criteria.getPost().equals("")))){
			// Agrego el inner join
			filter.addInnerJoin("person", "experiences");	
			// Filtro por compania
			if ((criteria.getCompany()!=null)&&(!criteria.getCompany().equals(""))) {
				filter.addParameterCondition("experiences.company", "like", criteria.getCompany());
			}
			// Filtro por Post
			if ((criteria.getPost()!=null)&&(!criteria.getPost().equals(""))) {
				filter.addParameterCondition("experiences.post", "like", criteria.getPost());
			}
			
		}
		// Si University o Career o FieldOfStudy no es nulo tengo que agregar un inner join 
		if (((criteria.getUniversity()!=null)&&(!criteria.getUniversity().equals("")))||
				((criteria.getCareer()!=null)&&(!criteria.getCareer().equals("")))    ||
				((criteria.getFieldOfStudy()!=null)&&(!criteria.getFieldOfStudy().equals("")))){
			
			// Agrego el Inner Join	
			filter.addInnerJoin("person", "studies");
			
			// Filtro por universidad o school para el caso de los filtros es indistinto.
			if ((criteria.getUniversity()!=null)&&(!criteria.getUniversity().equals(""))) {

				// Agrego el Left Join	 
				// Se debe agregar la tabla Studies "s" para poder hacer un Left Join con university
				filter.addFrom("Studies", "s");
				filter.addCondition("studies.id=s.id");
				filter.addLeftJoin("s", "university");
				
				Selector UniversitySelector = filter.getSelector().addChildSelector(Selector.CONJUNTION_OR);
				// Si la university es nula se busca por el campo school
				UniversitySelector.addCondition("(s.school like '"+criteria.getUniversity()+"') ");
				// Si la school es nula se busca por el campo universidad
				UniversitySelector.addCondition("(university.name like '"+criteria.getUniversity()+"') ");
			}
			
			if ((criteria.getCareer()!=null)&&(!criteria.getCareer().equals(""))) {
				filter.addParameterCondition("studies.career", "like", criteria.getCareer());
			}
			
			// Si FieldOfStudy no es nulo o blanco agrego la condicion
			if ((criteria.getFieldOfStudy()!=null)&&(!criteria.getFieldOfStudy().equals(""))) {
				filter.addParameterCondition("studies.studyType.id", "=", criteria.getFieldOfStudy());					
			}
		}
		
		// Si Skill no es nulo tengo que agregar un inner join
		if ((criteria.getSkill() != null) && (!criteria.getSkill().equals(""))) {
			// Agrego el Inner Join	
			filter.addInnerJoin("person", "skills");
			filter.addParameterCondition("skills.skill.name", "like", criteria.getSkill());
		}
		
		if (criteria.getVoidedType() == Criteria.VOID_NO)
			filter.addCondition("person.voided='F'");
		else if (criteria.getVoidedType() == Criteria.VOID_YES)
			filter.addCondition("person.voided='T'");
		
		// Si el criterio es una instancia de EmployeePersonSearchCriteria reviso las 
		// condiciones cargadas en EmployeePersonSearchCriteria
		//TODO: revisar si no es conveniente hacer un EmployeeSearcher que herede de PersonSearcher
		if (criteria instanceof EmployeePersonSearchCriteria) {
			EmployeePersonSearchCriteria employeeCriteria = (EmployeePersonSearchCriteria) criteria;
			filter.addFrom("Employee", "employee");
			filter.addCondition("employee.person.id = person.id");
			
			// Si Office Locations es distinto de nulo o vacio
			if ((employeeCriteria.getOfficeLocations() != null) && (employeeCriteria.getOfficeLocations().size() > 0)) {
				log.debug("Adding office locations to query");
				// Itero agregando sus hijos.
				Iterator iter = employeeCriteria.getOfficeLocations().iterator();
				if (iter.hasNext()) {
					Selector locationSelector = filter.getSelector()
						.addChildSelector("Location Selector", Selector.CONJUNTION_OR);
					while (iter.hasNext()) {
						Location location = (Location)iter.next();
						this.addEmployeeLocationAndChildrenToSelector(location, locationSelector);
					}
				}
			}
			
			// Si el job function es distinto de nulo, agrego el filtro
			if (employeeCriteria.getFunction() != null) {
				log.debug("Adding job function to query");
				Selector jobFuntionSelector = filter.getSelector().addChildSelector("Job function Selector", Selector.CONJUNTION_OR);
				this.addEmployeeJobFunctionToSelector(employeeCriteria.getFunction(),jobFuntionSelector);
			}
			
			if (employeeCriteria.getLoggedPersonFunction() != null) {
				Selector userJobFuntionSelector = filter.getSelector().addChildSelector("User Job Function Selector", Selector.CONJUNTION_AND);
				addRestrictionEmployeeJobFunctionToSelector(employeeCriteria.getLoggedPersonFunction(), userJobFuntionSelector);
			}
			
			//Agrego el filtro de activo
			Selector activeSelector = filter.getSelector().addChildSelector("Active Selector", Selector.CONJUNTION_AND);
			this.addEmployeeActiveToSelector(employeeCriteria.isActive(), activeSelector);
		}
		
		return(filter);
	}
	
	private void addPersonLocationAndChildrenToSelector(Location parent,
			Selector idSelector) {
			idSelector.addParameterCondition("person.contactInfo.homeAddress.location.id", "=", parent.getId());
			if (log.isDebugEnabled())
				log.debug("added implied criteria location: " + parent.getName());
			Collection children = CoreServiceLocator.getLocationService().findChildren(parent);
			Iterator iter = children.iterator();
			while (iter.hasNext()) {
				Location child = (Location)iter.next();
				this.addPersonLocationAndChildrenToSelector(child, idSelector);
			}
	}
	
	private void addEmployeeLocationAndChildrenToSelector(Location parent,
			Selector idSelector) {
			idSelector.addParameterCondition("employee.location.id", "=", parent.getId());
			if (log.isDebugEnabled())
				log.debug("added implied criteria location: " + parent.getName());
			Collection children = CoreServiceLocator.getLocationService().findChildren(parent);
			Iterator iter = children.iterator();
			while (iter.hasNext()) {
				Location child = (Location)iter.next();
				this.addEmployeeLocationAndChildrenToSelector(child, idSelector);
			}
	}
	
	private void addEmployeeJobFunctionToSelector(JobFunction jobFunction, Selector idSelector) {
		idSelector.addParameterCondition("employee.function.code", "=", jobFunction.getCode());
		if (log.isDebugEnabled())
			log.debug("added implied criteria job fuction: " + jobFunction.getCode());
	}
	
	private void addRestrictionEmployeeJobFunctionToSelector(JobFunction jobFunction, Selector idSelector) {
		idSelector.addParameterCondition("employee.function.weight", "<", jobFunction.getWeight().toString());
		idSelector.addParameterCondition("employee.function.weight", ">=", Integer.toString(0));
		if (log.isDebugEnabled())
			log.debug("added implied criteria job fuction: " + jobFunction.getCode());
	}
	
	private void addEmployeeActiveToSelector(boolean active, Selector idSelector) {
		if (active) {
			idSelector.addCondition("employee.dateEnd is null");
		} else {
			idSelector.addCondition("employee.dateEnd is not null");
		}
		if (log.isDebugEnabled())
			log.debug("added implied criteria active: " + active);
	}
	
	private void addParentDomainPrivGroup(Selector grpSelector, Domain childDomain, PersonPrivilegeGroupType groupType) {
		if (childDomain.getParent() != null) {
			Domain domain = childDomain.getParent();
			Selector selector = grpSelector.addChildSelector(Selector.CONJUNTION_AND);
			selector.addParameterCondition("personPrivilegeGroup.personPrivilegeGroupType.code", "=", groupType.getCode());
			selector.addCondition("personPrivilegeGroup.global='T'");
			addParentDomainPrivGroup(grpSelector, domain, groupType);
		}
	}

	
}
